package com.kmvc.message.database;

import java.io.File;
import java.util.List;

import android.app.Activity;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

import com.kmvc.message.activity.MessageApplication;
import com.kmvc.message.entity.StringEntity;

public class DbHelper extends SQLiteOpenHelper {

	public Context context;
	private static String DATABASE_NAME = "DataSMS";
	private static int DATABASE_VERSION = 1;

	public DbHelper(Context context) {

		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
		upDateTable();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	
		String sql = "Create table MessageHistory(id INTEGER PRIMARY KEY AUTOINCREMENT,Content varchar,"
				+ "sendDate varchar,area varchar,state int,IMG_URL varchar);";
		db.execSQL(sql);
		String sql0="Create table Img(id INTEGER PRIMARY KEY AUTOINCREMENT,IMG_ID varchar,"
				+ "LastDate varchar,IMG_URL varchar,IMG_URL2 varchar);";
		String sql00="Create table Area_setting(id INTEGER PRIMARY KEY AUTOINCREMENT,BUMEN_ID varchar,"
				+ "LastDate varchar,IMG_ID varchar,ZOOM varchar);";
		db.execSQL(sql0);
		db.execSQL(sql00);
	}

	/***
	 * 更新表结构
	 */
//	public void upDateTable() {
//		String sql = "Alter table MessageHistory add column area varchar";
//		// String sql1 = "Alter table MessageHistory add column state int";
//		// String sql2 =
//		// "Alter table MessageHistory add column MessageID varchar";
//		SQLiteDatabase db = this.getWritableDatabase();
//		try {
//			db.execSQL(sql);
//		} catch (Exception e) {
//			e.toString();
//		}
//		// try {
//		// db.execSQL(sql1);
//		//
//		// } catch (Exception e) {
//		// e.toString();
//		// }
//		// try {
//		// db.execSQL(sql2);
//		//
//		// } catch (Exception e) {
//		// e.toString();
//		// }
//		db.close();
//
//	}
	
	
	public void upDateTable() {
		
		String sql000="Alter table MessageHistory add column IMG_URL varchar";
		String sql0="Create table Img(id INTEGER PRIMARY KEY AUTOINCREMENT,IMG_ID varchar,"
				+ "LastDate varchar,IMG_URL varchar,IMG_URL2 varchar);";
		String sql00="Create table Area_setting(id INTEGER PRIMARY KEY AUTOINCREMENT,BUMEN_ID varchar,"
				+ "LastDate varchar,IMG_ID varchar,ZOOM varchar);";
		String sql = "Alter table MessageHistory add column state int";
		// String sql1 = "Alter table MessageHistory add column state int";
		// String sql2 =
		// "Alter table MessageHistory add column MessageID varchar";
		SQLiteDatabase db = this.getWritableDatabase();
		try 
		{
			db.execSQL(sql000);
			db.execSQL(sql0);
			db.execSQL(sql00);
			db.execSQL(sql);
			String sql1="update MessageHistory set state=0";
			try 
			{
				db.execSQL(sql1);
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} catch (Exception e) 
		{
			e.toString();
		}
	}
	

	/***
	 * 插入数据
	 * 
	 * @param Content
	 *            内容
	 * @param sendDate
	 *            发送时间
	 * @param area
	 *            区域
	 * @param messageID
	 *            信息ID
	 */
//	public void inserthistory(String Content, String sendDate, String area) {
//		SimpleDateFormat formatter = new SimpleDateFormat(// 时间进行格式化
//				"yyyy-MM-dd HH:mm:ss.fff");
//
//		Date newdate = null;
//		try {
//			newdate = formatter.parse(sendDate);
//		} catch (ParseException e1) {
//
//			e1.printStackTrace();
//		}
//		sendDate = formatter.format(newdate);
//		SQLiteDatabase db = this.getWritableDatabase();
//		// String sql = "insert into MessageHistory(Content,sendDate) values('"
//		// + Content + "','" + sendDate + "')";
//		// 插入信息状态为1，表示未读
//		String sql = "insert into MessageHistory(Content,sendDate,area) select '"
//				+ Content
//				+ "','"
//				+ sendDate
//				+ "','"
//				+ area
//				+ "' where not exists(select * from MessageHistory where Content='"
//				+ Content
//				+ "' and sendDate='"
//				+ sendDate
//				+ "' and area='"
//				+ area + "')";
//		try {
//			db.execSQL(sql);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//
//	}

	/**
	 * 自动清理信息
	 */
	public void autodelete(String days)
	{
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "delete from MessageHistory where substr(sendDate,0,11)<date('now','-"+days+" day','+8 hour')";
		try {
			//Log.e("DbHelper124", sql);
			db.execSQL(sql);			 
		} catch (Exception e) {
			e.printStackTrace();
		}
		db.close();
	}
	
	
	/***
	 * 删除所有信息
	 */
	public void deleteall() {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "delete from MessageHistory";
		String sqlString="update sqlite_sequence set seq=0 where name='MessageHistory'";
		try {
			db.execSQL(sql);
			db.execSQL(sqlString);
		} catch (Exception e) {
			e.printStackTrace();
		}
		db.close();
	}

	/***
	 * 删除区域下的所有信息
	 * 
	 * @param area
	 */
	public void delteall(String area, String datesString) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "";
		if (datesString.contains(":")) {
			sql = "delete from MessageHistory where area='" + area
					+ "' and substr(sendDate,0,11)=date('now','+8 hour')";
		} else {
			sql = "delete from MessageHistory where area='" + area
					+ "' and substr(sendDate,0,11)<>date('now','+8 hour')";
		}
		try {
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		db.close();
	}

	/***
	 * 删除单条信息
	 * 
	 * @param ids
	 */
	public void deletebyid(String ids) {
		int id = Integer.parseInt(ids);
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "delete from MessageHistory where id=" + id + "";
		try {
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		db.close();
	}

	/***
	 * 获取当天信息条数
	 * 
	 * @return
	 */
	public int getTodayMessageCount() {
		SQLiteDatabase db = this.getReadableDatabase();
		String sqlString = "select count(*) from MessageHistory where substr(sendDate,0,11)=date('now','+8 hour')";
		Cursor cursor = db.rawQuery(sqlString, null);
		int result = 0;
		while (cursor.moveToNext()) {
			result = Integer.parseInt(cursor.getString(0));
		}
		cursor.close();
		db.close();
		return result;
	}

	/***
	 * 检索历史记录
	 * 
	 * @return
	 */
	public Cursor selecthistory() {
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "select id,Content,sendDate from MessageHistory order by sendDate desc";
		Cursor cursor = db.rawQuery(sql, null);
		return cursor;
	}

	/***
	 * 根据区域显示信息
	 * 
	 * @param area
	 *            区域
	 * @return
	 */
	public Cursor selecthistorynew(String area, String datString) {
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "";
		if (area.equals("昆明沃创科技")) {// 以前没有增加地区的信息
			if (datString.contains(":"))// 当天的记录
			{
				sql = "select id,Content,sendDate from MessageHistory where (area='null' or "
						+ "area is null or area='"+area+"') and substr(sendDate,0,11)=date('now','+8 hour') order by sendDate desc";
			} else {
				sql = "select id,Content,sendDate from MessageHistory where (area='null' or "
						+ "area is null or area='"+area+"') and substr(sendDate,6,5)='"+datString+"' order by sendDate desc";
			}
		} else {

			if (datString.contains(":"))// 当天的记录
			{
				sql = "select id,Content,sendDate from MessageHistory where area='"
						+ area
						+ "' and substr(sendDate,0,11) =date('now','+8 hour')  order by sendDate desc";
			} else {
				sql = "select id,Content,sendDate from MessageHistory where area='"
						+ area
						+ "' and substr(sendDate,6,5)='"+datString+"'  order by sendDate desc";
			}
		}
		Cursor cursor = db.rawQuery(sql, null);
		return cursor;
	}

	/***
	 * 信息分组
	 * 
	 * @return
	 */
	public Cursor selectGroup(String key) {
		SQLiteDatabase db = this.getReadableDatabase();
		if (!key.equals("")) {
			//String keyString=Encryption.encode(key);
			String sqlString = " SELECT AA.*,BB.IMG_URL2  FROM ( "					
					+"select case area  when 'null' then '昆明沃创科技' when  area then area else '昆明沃创科技' end "
					+ "as 'area', Content,case substr(sendDate,0,11) when date('now','+8 hour') then"
					+ " substr(sendDate,12,5) else substr(sendDate,6,5) end as 'dates',count(id),sum(CASE state when 0 THEN 0 ELSE 1 END ) num"
					+ " from MessageHistory where (area like'%"+key+"%' or sendDate like '%"+key+"%')  group by area,substr(sendDate,0,11) "
				    + "order by sendDate desc) AA "
					+" LEFT JOIN   (SELECT BUMEN_ID,IMG_URL2 FROM Area_setting A,Img B WHERE  A.IMG_ID=B.IMG_ID )BB"
					+" ON AA.area=BB.BUMEN_ID ";
			Cursor cursor = db.rawQuery(sqlString, null);
			return cursor;
		}
		else {
			String sqlString =" SELECT AA.*,BB.IMG_URL2  FROM ( "					
					+"select case area  when 'null' then '昆明沃创科技' when  area then area else '昆明沃创科技' end "
					+ "as 'area', Content,case substr(sendDate,0,11) when date('now','+8 hour') then"
					+ " substr(sendDate,12,5) else substr(sendDate,6,5) end as 'dates',count(id),sum(CASE state when 0 THEN 0 ELSE 1 END ) num"
					+ " from MessageHistory group by area,substr(sendDate,0,11) order by sendDate desc ) AA "
					+" LEFT JOIN   (SELECT BUMEN_ID,IMG_URL2 FROM Area_setting A,Img B WHERE  A.IMG_ID=B.IMG_ID )BB"
					+" ON AA.area=BB.BUMEN_ID ";
			Cursor cursor = db.rawQuery(sqlString, null);
			return cursor;
		}
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//

	}

	public void inserthistorybyListnew(List<StringEntity> mStringList)
	{	
		//Date beginDate=new Date();
		SQLiteDatabase db = getWritableDatabase();
		db.beginTransaction();
		try {
			for(int i=0;i<mStringList.size();i++)
			{
				String Content = mStringList.get(i).getContent();
				Content = Content.replace("*", "<");
				Content = Content.replace("^", ">");				 
				String sendDate = mStringList.get(i).getStart_Time();
				String area = mStringList.get(i).getArea();	
				String Image = mStringList.get(i).Image;	
				
				if(Content.equals("")&&Image!=null&&!Image.equals(""))
				{
					Content="<Image>"+Image+"</Image>";
				}
				
				String sql = "insert into MessageHistory(Content,sendDate,area) select '"
						+ Content
						+ "','"
						+ sendDate
						+ "','"
						+ area
						+ "' where not exists(select 1 from MessageHistory where Content='"
						+ Content
						+ "' and sendDate='"
						+ sendDate
						+ "' and area='"
						+ area + "')";
				

				
				try 
				{
					db.execSQL(sql);
					if(Image!=null&&!Image.equals(""))
					{
						SharedPreferences sp =((Activity)context).getSharedPreferences("com.kmvc.message_preferences", 0);
						if(sp.getBoolean("AutoDownloadImg", false))
						{
							String url=Image;
							MessageApplication application=(MessageApplication)((Activity)context).getApplication();
							application.LoadReportImg(Content, sendDate, area,url,this);
						}
					}
					
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			db.endTransaction();
			db.close();
		}
//		Date enddaDate=new Date();	
//		Log.e("xiancha",(enddaDate.getTime()-beginDate.getTime())/1000+"s");
	}
	
	
	
	public void updateReadState(String area,String Date) 
	{
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "UPDATE MessageHistory SET state=0 "
				+"WHERE area='"+area+"' AND (case substr(sendDate,0,11) when date('now','+8 hour') then "
				+"substr(sendDate,12,5) else substr(sendDate,6,5) end)='"+Date+"'";
		if(Date.indexOf(":")>-1)
		{
			sql="UPDATE MessageHistory SET state=0 "
			   +"WHERE area='"+area+"' AND substr(sendDate,0,11)=date('now','+8 hour')";
		}
		try {
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		db.close();
	}
	
	
	/*
	 * flag:1 获取图片最后的更新时间
	 * flag:2 获取分组设置最后的更新时间
	 */
	public Cursor getLastDate(int flag)
	{
		Cursor cursor=null;
		SQLiteDatabase db = this.getReadableDatabase();
		String sqlString="";
		if(flag==1)
		{
			sqlString="SELECT LastDate  FROM Img order by LastDate desc";
		}
		else if(flag==2)
		{
			sqlString="SELECT LastDate  FROM Area_setting order by LastDate desc";
		}
		try 
		{
			cursor=db.rawQuery(sqlString, null);
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return cursor;
	}
	
	public void insertImg(List<StringEntity> mStringList)
	{	
		SQLiteDatabase db = getWritableDatabase();
		db.beginTransaction();
		try {
			for(int i=0;i<mStringList.size();i++)
			{
				String IMG_ID=mStringList.get(i).IMG_ID;
				String IMG_URL=mStringList.get(i).IMG_URL;
				String INPUT_DATE=mStringList.get(i).INPUT_DATE;
				String sql = "insert into Img(IMG_ID,IMG_URL,LastDate) values('"
						+IMG_ID+"','"+IMG_URL+"','"+INPUT_DATE+"')";
				db.execSQL(sql);
			}
			db.setTransactionSuccessful();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			db.endTransaction();
			db.close();
		}
	}
	
	public void insertSetting(List<StringEntity> mStringList)
	{	
		SQLiteDatabase db = getWritableDatabase();
		db.beginTransaction();
		try {
			for(int i=0;i<mStringList.size();i++)
			{
				String BUMEN_ID=mStringList.get(i).BUMEN_ID;
				String IMG_ID=mStringList.get(i).IMG_ID;
				String UPDATE_TIME=mStringList.get(i).UPDATE_TIME;
				String sql = "insert into Area_setting(BUMEN_ID,IMG_ID,LastDate) values('"
						+BUMEN_ID+"','"+IMG_ID+"','"+UPDATE_TIME+"')";
				db.execSQL(sql);
			}
			db.setTransactionSuccessful();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			db.endTransaction();
			db.close();
		}
	}
	
	
	public Cursor getImgList()
	{
		Cursor cursor=null;
		SQLiteDatabase db = this.getReadableDatabase();
		String sql="select *  from img where IMG_URL2 IS NULL";
		try 
		{
			cursor=db.rawQuery(sql, null);
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		return cursor;
	}
	
	public void UpdateImg(String IMG_ID,String URL2)
	{
		SQLiteDatabase db = this.getReadableDatabase();
		String sql="update img set IMG_URL2='"+URL2+"' where IMG_ID="+IMG_ID;
		try 
		{
			db.execSQL(sql);
			
		} catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	
	
	public void UpdateReportImg(String Content,String sendDate,String area,String URL)
	{
		SQLiteDatabase db = this.getReadableDatabase();
		String sql="update  MessageHistory set IMG_URL='"+URL+"' where Content='"+Content
				  +"' AND sendDate='"+sendDate+"' AND area='"+area+"'";
		try 
		{
			db.execSQL(sql);
			
		} catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	
	public void DeleteSdImgState(String IMG_URL2)
	{
		String sql="UPDATE Img SET IMG_URL2=null where IMG_URL2='"+IMG_URL2+"'";
		SQLiteDatabase db = this.getReadableDatabase();
		try 
		{
			String SDPATH = Environment.getExternalStorageDirectory().getPath();
			String path=SDPATH+"/KMVC_MESSAGE/"+IMG_URL2;
			File mfile=new File(path);
			if(!mfile.exists())
			{
				Log.e("kmvc", "文件不存在");
				db.execSQL(sql);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	
	
	

}
